//
//  SQLiteDataBaseManager.m
//  练习SQL
//
//  Created by 苗钧豪 on 16/8/8.
//  Copyright © 2016年 苗钧豪. All rights reserved.
//

#import "SQLiteDataBaseManager.h"
#import "Student.h"
@implementation SQLiteDataBaseManager
// 单例创建方式，只有一个对象
// 内存管理中，单例不能被释放
+ (SQLiteDataBaseManager *)sharedManager {
    static SQLiteDataBaseManager *manager = nil;
    // 保证线程安全情况下，只执行一次
    static dispatch_once_t onceToken;
    dispatch_once(&onceToken, ^{
        manager = [[SQLiteDataBaseManager alloc]init];
    });
    return manager;
}
- (BOOL)openSQLite {
    NSArray *pathArr = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *document = [pathArr firstObject];
    // 数据库SQLite3文件类型.db
    NSString *dbPath = [document stringByAppendingPathComponent:@"student.db"];
    NSLog(@"~~~~~~~~~~~~~~~~~~~~~%@",dbPath);
    // UTF8String属性，将OC字符串转换成C语言字符串
    // 参数1：数据库文件路径
    // 参数2：只想指针的指针，该指针可以被修改 数据库管理指针
    int result = sqlite3_open([dbPath UTF8String], &dbPoint);
    // sqlite_open 当路径文件不存在时，文件会被SQLite创建并打开
    // 当路径文件存在时，文件被直接打开
    // sqlite3_open 返回值为 int 型；0：SQLITE_OK 代表成功，1：代表失败
    return [self isOperationResult:result alertText:@"打开数据库"];
}

- (BOOL)closeSQLite {
    int result = sqlite3_close(dbPoint);
    return [self isOperationResult:result alertText:@"关闭数据库"];

}

- (BOOL)createtable {
    // SQL语句
    // 创建表
    // create table 表名 （字段1 类型 约束，字段2，字段3。。）
    // 常用的约束：
    // primary key 一个表，只能有一个主键
    // autoincrement 添加完该字段，系统会自己创建一个数据库的表 sqlite_sequence,用来保存自增的值
    // not null 不能为空，为空会报SQL错误
    
    // 删除表
    // SQL 语句：drop table 表名
    NSString *creatrSQL = @"create table stu2 (stu_id integer primary key autoincrement, stu_name text not null)";
    int result = sqlite3_exec(dbPoint, [creatrSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"创建表"];
}

- (BOOL)insertStu:(Student *)stu {
    NSString *insertSQL = [NSString stringWithFormat:@"insert into stu1 values ('%@', '%@', '%@','%@')",stu.stu_name,stu.stu_gender,stu.stu_number,stu.stu_image];
    // 自增字段 autoincrement
    // 默认如果填入null，值会从1开始
    // 如果填入其他值，会从其他值开始，例：填入110从110开始计算，并进行存储
    // 存储在sqlite_sequence中
//        NSString *insertSQL = @"insert into stu1 (stu_id, stu_name) values (NULL, 'XX')";
    int result = sqlite3_exec(dbPoint, [insertSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"添加数据"];
}

//- (BOOL)deleteStu:(Student *)stu {
//    NSString *deleteSQL = [NSString stringWithFormat:@"delete from stu1 where stu_id=%ld",stu.stu_id];
//    int result = sqlite3_exec(dbPoint, [deleteSQL UTF8String], NULL, NULL, NULL);
//    return [self isOperationResult:result alertText:@"删除某数据成功"];
//}

- (BOOL)deleteAll {
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from stu1 where 1=1"];
    int result = sqlite3_exec(dbPoint, [deleteSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"删除全部数据成功"];
}

- (BOOL)updateOldName:(NSString *)oldName newName:(NSString *) newName {
    // update 表名 set 字段名 = 新值 where 字段名 = 旧值
    NSString *updateSQL = [NSString stringWithFormat:@"update stu1 set stu_name = '%@' where stu_name = '%@'",newName,oldName];
    int result = sqlite3_exec(dbPoint, [updateSQL UTF8String], NULL, NULL, NULL);
    return [self isOperationResult:result alertText:@"更新数据"];
}

- (__kindof NSArray *)selectAll {
    // select 字段名，字段名...from 表名 where 条件
    NSString *selectAll = @"select * from stu1";
    sqlite3_stmt *stmt = NULL;
    // 参数1：数据库指针
    // 参数2：数据库SQL语句
    // 参数3：SQL语句长度，-1不限制长度
    // 参数4：替身，查询出的内容放入替身中，并将替身返回
    NSMutableArray *array = [NSMutableArray array];
    int result = sqlite3_prepare(dbPoint, [selectAll UTF8String], -1, &stmt, NULL);
    // 如果查询结果成功
    if (result == SQLITE_OK) {
        // 循环，遍历替身内容
        // step查询替身中有木有其他数据，当包含有其他数据时，结果为 SQLITE_ROW
        // 当不包含其他数据是，结果为 SQLITE_DONE
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            // 循环体中取对应的每一列数据
            // 取出第0列的数据，根据类型选择数据
            const unsigned char *stu_name = sqlite3_column_text(stmt, 0);
            // 取出第1列的数据，根据类型选择数据
            const unsigned char *stu_gender = sqlite3_column_text(stmt, 1);
            const unsigned char *stu_number = sqlite3_column_text(stmt, 2);
            const unsigned char *stu_image = sqlite3_column_text(stmt, 3);
            Student *stu = [[Student alloc]init];
            stu.stu_name = [NSString stringWithUTF8String:(const char *)stu_name];
            stu.stu_gender = [NSString stringWithUTF8String:(const char *)stu_gender];
            stu.stu_number = [NSString stringWithUTF8String:(const char *)stu_number];
            stu.stu_image = [NSString stringWithUTF8String:(const char *)stu_image];
            [array addObject:stu];
        }
    }
    // 循环结束后销毁替身
    sqlite3_finalize(stmt);
    return array;
}

//- (__kindof NSArray *)selectorStu:(Student *)stu {
//    NSString *selectAll = [NSString stringWithFormat:@"select * from stu1 where stu_name = '%@'",stu.stu_name];
//    sqlite3_stmt *stmt = NULL;
//        NSMutableArray *array = [NSMutableArray array];
//    int result = sqlite3_prepare(dbPoint, [selectAll UTF8String], -1, &stmt, NULL);
//    // 如果查询结果成功
//    if (result == SQLITE_OK) {
//        // 循环，遍历替身内容
//        while (sqlite3_step(stmt) == SQLITE_ROW) {
//            // 取出第0列的数据，根据类型选择数据
//            int stu_id = sqlite3_column_int(stmt, 0);
//            // 取出第1列的数据，根据类型选择数据
//            const unsigned char *stu_name = sqlite3_column_text(stmt, 1);
//            Student *stu = [[Student alloc]init];
//            stu.stu_id = stu_id;
//            stu.stu_name = [NSString stringWithUTF8String:(const char *)stu_name];
//            [array addObject:stu];
//        }
//    }
//    // 循环结束后销毁替身
//    sqlite3_finalize(stmt);
//    return array;
//}

- (BOOL)isOperationResult:(int)result alertText:(NSString *)text {
    BOOL isOperation = NO;
    if (result == SQLITE_OK) {
        NSLog(@"%@成功",text);
        isOperation = YES;
    } else {
        NSLog(@"%@失败————%d",text,result);
        isOperation = NO;
    }
    return isOperation;
}

@end
